<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
 defined ('IN_AGORA') or die;
	class SubscriptionModel extends Model
	{
		function __construct()
		{
			parent::__construct('subscriptions');
		}

		function isSubscribed($user_id, $topic_id)
		{
			$this->db->bind('user_id',$user_id,'integer');
			$this->db->bind('topic_id',$topic_id,'integer');

			$this->db->setQuery('SELECT COUNT(*) FROM ##__subscriptions'.
							' WHERE user_id=:user_id'.
							' AND topic_id=:topic_id');
			return intval($this->db->loadResult());
		}

		function delete($user_id, $topic_id)
		{
			$this->db->bind('user_id',$user_id,'integer');
			$this->db->bind('topic_id',$topic_id,'integer');

			$this->db->setQuery('DELETE FROM ##__subscriptions'.
							' WHERE user_id=:user_id'.
							' AND topic_id=:topic_id');
			$this->db->query();
		}

		function deleteByEmail($email, $topic_id)
		{
			$this->db->bind('poster_email',$email,'string');
			$this->db->bind('topic_id',$topic_id,'integer');

			$this->db->setQuery('UPDATE ##__posts'.
							' SET poster_email=""'.
							' WHERE poster_email=:poster_email'.
							' AND topic_id=:topic_id');
			$this->db->query();
		}
		
		function delete_forum($user_id, $forum_id)
		{
			$this->db->bind('user_id',$user_id,'integer');
			$this->db->bind('forum_id',$forum_id,'integer');

			$this->db->setQuery('DELETE FROM ##__subscriptions'.
							' WHERE user_id=:user_id'.
							' AND forum_id=:forum_id');
			$this->db->query();
		}


		function loadSubscribers($topic_id, $forum_id)
		{
			$this->db->bind('topic_id',$topic_id,'integer');
			$this->db->bind('forum_id',$forum_id,'integer');

			$this->db->setQuery('SELECT DISTINCT user.id, user.email, user.username, user.disp_posts, j_u.name, s.emailed, s.topic_id, s.forum_id'.
							' FROM ##__users AS user'.
							' INNER JOIN ##__subscriptions AS s'.
							'  ON s.user_id = user.id'.
							' LEFT JOIN #__users AS j_u'.
							'  ON j_u.id = user.jos_id'.
							' WHERE (s.topic_id=:topic_id OR s.forum_id=:forum_id)'.
							' UNION ALL ('.
							'  SELECT 0 AS id, gp.poster_email AS email, gp.poster AS username, 0 AS disp_posts, gp.poster AS name, 0 AS emailed, gp.topic_id, gt.forum_id AS forum_id'.
							'  FROM ##__posts AS gp'.
							'  LEFT JOIN ##__topics AS gt'.
							'   ON gt.id = gp.topic_id'.
							'  WHERE ('.
							'   gp.poster_id=0'.
							'   AND gp.topic_id=:topic_id'.
							'   AND gp.poster_email IS NOT NULL'.
							'   AND gp.poster_email LIKE "%@%"'.
							'  )'.
							'  GROUP BY gp.id, gp.poster_email'.
							' )'
			);
			return $this->db->loadAssocList();
		}
		
		function loadSubscribersModsAdmins($topic_id, $forum_id) {
			$this->db->bind('topic_id',$topic_id,'integer');
			$this->db->bind('forum_id',$forum_id,'integer');

			$this->db->setQuery('SELECT au.id, au.email, au.username, au.disp_posts, ju.name, s.emailed, s.topic_id, s.forum_id
							FROM #__agora_subscriptions s
								JOIN #__agora_users au
									ON s.user_id = au.id
								JOIN #__users ju
									ON ju.id = au.jos_id
								JOIN #__agora_user_group aug 
									ON aug.user_id = au.id
								JOIN #__agora_group_forum agf
									ON aug.group_id = agf.group_id
										AND aug.role_id = agf.role_id
										AND agf.forum_id = :forum_id
								JOIN #__agora_permissions p
									ON p.id = agf.access_id
								JOIN #__agora_roles r 
									ON aug.role_id = r.id
								JOIN #__agora_forums f
									ON agf.forum_id = f.id
							WHERE r.name IN (\'moderator\', \'admin\')
								AND p.post_approve = 1
								AND f.use_moderation = 1
								AND (s.topic_id=:topic_id OR s.forum_id=:forum_id)');
			return $this->db->loadAssocList();			
		}
		
		function updateSubscriberEmailed($user_id, $topic_id, $forum_id, $emailed)
		{
			foreach (array('user_id', 'topic_id', 'forum_id', 'emailed') as $var) $$var = (int) $$var;
            $this->db->setQuery("UPDATE ##__subscriptions SET emailed=$emailed
                            	WHERE user_id = $user_id AND topic_id = $topic_id AND forum_id=$forum_id");
            return $this->db->query();
		}
		
		function addAllMemRolToAllThereForums($role)
		{
				$this->db->setQuery('SELECT group_forum.forum_id, user_group.user_id'.
								' FROM ##__user_group AS user_group'.
								' INNER JOIN ##__group_forum AS group_forum'.
								'  ON user_group.group_id = group_forum.group_id'.
								'  AND user_group.role_id = group_forum.role_id'.
								' INNER JOIN ##__permissions AS permissions'.
								'  ON permissions.id = group_forum.access_id'.
				                ' INNER JOIN ##__roles as r ON user_group.role_id=r.id'.
								' WHERE r.name=\''.$role.'\'' );
				$result = $this->db->loadAssocList();
				foreach ($result as $res)
				{
				    $this->add(array('user_id'=>$res['user_id'],'forum_id'=>$res['forum_id']));
				}
		}
		
		function removeAllMemRolToAllThereForums($role)
		{
				$this->db->setQuery('SELECT group_forum.forum_id, user_group.user_id'.
								' FROM ##__user_group AS user_group'.
								' INNER JOIN ##__group_forum AS group_forum'.
								'  ON user_group.group_id = group_forum.group_id'.
								'  AND user_group.role_id = group_forum.role_id'.
								' INNER JOIN ##__permissions AS permissions'.
								'  ON permissions.id = group_forum.access_id'.
				                ' INNER JOIN ##__roles as r ON user_group.role_id=r.id'.
								' WHERE r.name=\''.$role.'\'' );
				$result = $this->db->loadAssocList();
				foreach ($result as $res)
				{
				    $this->db->setQuery("DELETE FROM ##__subscriptions WHERE user_id=".$res['user_id']." AND forum_id=".$res['forum_id']);
		            $this->db->query();
				}
		}
		
		function addAllMemRolToForums($role, $forums)
		{
			$this->db->setQuery('SELECT u.id FROM ##__users as u INNER JOIN ##__user_group as u_g ON u.id=u_g.user_id INNER JOIN ##__roles as r ON u_g.role_id=r.id WHERE u_g.group_id=1 AND r.name=\''.$role.'\' ');
			$admins = $this->db->loadResultArray();
		    foreach ($admins as $u_id)
		        foreach ($forums as $forum)
		        {
		            $this->add(array('user_id'=>$u_id,'forum_id'=>$forum['id']));
		        }
		}
		
		function removeAllMemRolFromForums($role, $forums)
		{
			$this->db->setQuery('SELECT u.id FROM ##__users as u INNER JOIN ##__user_group as u_g ON u.id=u_g.user_id INNER JOIN ##__roles as r ON u_g.role_id=r.id WHERE u_g.group_id=1 AND r.name=\''.$role.'\' ');
			$admins = $this->db->loadResultArray();
		    foreach ($admins as $u_id)
		        foreach ($forums as $forum)
		        {
		            $this->db->setQuery("DELETE FROM ##__subscriptions WHERE user_id=$u_id AND forum_id=".$forum['id']);
		            $this->db->query();
		        }
		}
		
		function getSubscribedTopics($user_id, $per_page = null, $page = null)
		{
			$limit = $this->getLimit($per_page, $page);
			
			$this->db->bind('user_id',$user_id,'integer');

			$this->db->setQuery('SELECT topics.id, topics.subject, topics.poster, topics.num_replies'.
							' FROM ##__topics AS topics'.
							' INNER JOIN ##__subscriptions AS s'.
							' ON s.topic_id = topics.id'.
							' WHERE s.user_id=:user_id AND topics.approved = 1 ' . $limit );
			return $this->db->loadAssocList();
		}
		
		function countSubscribedTopics($user_id)
		{
			$this->db->bind('user_id',$user_id,'integer');

			$this->db->setQuery('SELECT COUNT(*)'.
							' FROM ##__topics AS topics'.
							' INNER JOIN ##__subscriptions AS s'.
							' ON s.topic_id = topics.id'.
							' WHERE s.user_id=:user_id  AND topics.approved = 1 ');
			return $this->db->loadResult();
		}
		
		function getSubscribedForums($user_id)
		{
			$this->db->bind('user_id',$user_id,'integer');

			$this->db->setQuery('SELECT forums.id, forums.forum_name, forums.num_posts, forums.num_topics,'.
							' forums.forum_desc FROM ##__forums AS forums'.
							' INNER JOIN ##__subscriptions AS s'.
							' ON s.forum_id = forums.id'.
							' WHERE s.user_id=:user_id ');
			return $this->db->loadAssocList();
		}
		
		function countSubscribedForums($user_id)
		{
			$this->db->bind('user_id',$user_id,'integer');

			$this->db->setQuery('SELECT COUNT(*)'.
							' FROM ##__forums AS forums'.
							' INNER JOIN ##__subscriptions AS s'.
							' ON s.forum_id = forums.id'.
							' WHERE s.user_id=:user_id ');
			return $this->db->loadResult();
		}
		
		function addAdminsToUnapproved() {
			$this->db->setQuery('SELECT group_forum.forum_id, user_group.user_id
								FROM #__agora_user_group AS user_group
									INNER JOIN #__agora_group_forum AS group_forum 
										ON user_group.group_id = group_forum.group_id
											AND user_group.role_id = group_forum.role_id
									INNER JOIN #__agora_permissions AS permissions 
										ON permissions.id = group_forum.access_id
									INNER JOIN #__agora_roles AS r 
										ON user_group.role_id = r.id
									INNER JOIN #__agora_forums f
										ON group_forum.forum_id = f.id
								WHERE r.name IN (\'moderator\', \'admin\')
									AND permissions.post_approve = 1
									AND f.use_moderation = 1');
			$result = $this->db->loadAssocList();
			foreach ($result as $res) {
				$this->add(array('user_id' => $res['user_id'], 'forum_id' => $res['forum_id']));
			}
		}

		function removeAdminsFromUnapproved() {
			$this->db->setQuery('SELECT group_forum.forum_id, user_group.user_id
								FROM #__agora_user_group AS user_group
									INNER JOIN #__agora_group_forum AS group_forum 
										ON user_group.group_id = group_forum.group_id
											AND user_group.role_id = group_forum.role_id
									INNER JOIN #__agora_permissions AS permissions 
										ON permissions.id = group_forum.access_id
									INNER JOIN #__agora_roles AS r 
										ON user_group.role_id = r.id
									INNER JOIN #__agora_forums f
										ON group_forum.forum_id = f.id
								WHERE r.name IN (\'moderator\', \'admin\')
									AND permissions.post_approve = 1
									AND f.use_moderation = 1');
			$result = $this->db->loadAssocList();
		    foreach ($result as $res) {
				$this->db->setQuery('DELETE FROM #__agora_subscriptions WHERE user_id = ' . $this->db->Quote($res['user_id']) . ' AND forum_id = ' . $this->db->Quote($res['forum_id']));
				$this->db->query();
			}
		}
	}

